/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package giormi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.JOptionPane;
import javax.swing.JTable;

/**
 *
 * @author Tim
 */
public class viewStocks {
    private String SQLStatement;
    private Connection conn;
    private String URL;
    private Statement stmt;
    public ResultSet rset;
    protected JTable main;
    
    public viewStocks(JTable stocks){
        this.main = stocks;
    }
    public void connect(){
        try{
            DBConnection db = new DBConnection();
            conn = DBConnection.get();
            stmt = conn.createStatement();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void disconnect(){
        try{
            stmt.close();
            conn.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
    
    public void viewStocks()
    {

        
        int i=0,nCtr=0;
            connect();
            //3
            SQLStatement = "SELECT stock.idstock, stock.s_dateposted, stock.s_datereceived, stock.s_quantity, stock.s_balance, product.p_name, product.p_packing,"
                         + "product.p_netweight, product.p_status, product.p_barcode, stock.s_quedan, stock.s_rrnum, stock.s_proddate, "
                         + "stock.s_expdate, stock.s_lotnum, stock.s_notes "
                         + "FROM stock, product "
                         + "WHERE stock.s_idproduct = product.idproduct "
                         + "AND stock.s_balance > 0 "
                         + "ORDER BY stock.idstock"; 
            try {
            rset = stmt.executeQuery(SQLStatement);
            while(rset.next())
                nCtr++;
            rset.close();
            rset = stmt.executeQuery(SQLStatement);
            
            while(rset.next()){
                main.setValueAt(rset.getInt("stock.idstock"),i,0);
                main.setValueAt(rset.getString("stock.s_dateposted"),i,1);
                main.setValueAt(rset.getString("stock.s_datereceived"),i,2);
                main.setValueAt(rset.getInt("stock.s_quantity"),i,3);
                main.setValueAt(rset.getInt("stock.s_balance"),i,4);
                main.setValueAt(rset.getString("product.p_name"),i,5);
                main.setValueAt(rset.getString("product.p_packing"),i,6);
                main.setValueAt(rset.getString("product.p_netweight"),i,7);
                main.setValueAt(rset.getString("product.p_status"),i,8);
                main.setValueAt(rset.getString("product.p_barcode"),i,9);
                main.setValueAt(rset.getString("stock.s_quedan"),i,10);
                main.setValueAt(rset.getString("stock.s_rrnum"),i,11);
                main.setValueAt(rset.getString("stock.s_proddate"),i,12);
                main.setValueAt(rset.getString("stock.s_expdate"),i,13);
                main.setValueAt(rset.getString("stock.s_lotnum"),i,14);
                main.setValueAt(rset.getString("stock.s_notes"),i,15);
                i++;

            }

            rset.close();
            //5
          disconnect();
            }
             catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
}
